﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Sql;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using UtilsLib.UtilsLog;


namespace UtilsLib.UtilsDataAccess
{
    public class DBConnection
    {
        private SqlDataReader _reader;
        private SqlCommand _cmd;
        private SqlConnection _con;
        private string _connString;
        private bool _isConOpen;
        private bool _isReaderOpen;
        private int _timeout;

        /// <summary>
        /// Init the object DBConnection
        /// </summary>
        /// <param name="connectionString">The connections informations</param>
        public DBConnection(string connectionString)
        {
            this._connString = connectionString;
            this._con = new SqlConnection(this._connString);
            this._isConOpen = false;
            this._isReaderOpen = false;
            this._timeout = 0;
        }

        public DBConnection()
        {
            this._isConOpen = false;
            this._isReaderOpen = false;
            this._timeout = 0;
        }


        /// <summary>
        /// Init the object's reader with the command
        /// </summary>
        /// <param name="strbRequest">The command returning the data</param>
        public SqlDataReader ExecReader(StringBuilder strbRequest)
        {
            return ExecReader(new SqlCommand(strbRequest.ToString()));
        }

        /// <summary>
        /// Init the object's reader with the request
        /// </summary>
        /// <param name="strbRequest">The request returning the data</param>
        public SqlDataReader ExecReader(SqlCommand cmd)
        {
            try
            {
                if (this._isConOpen)
                {
                    this.ReleaseReader();
                    this._cmd = cmd;
                    this._cmd.Connection = this._con;
                    this._cmd.CommandTimeout = (this._timeout != 0 ? this._timeout : this._cmd.CommandTimeout);
                    this._reader = this._cmd.ExecuteReader();
                    this._isReaderOpen = true;

                }
                else
                {
                    LogToFile.WriteError(" [DBConnection.ExecReader] Trying to Execute a Query while the Connection is Closed. ");
                }
            }
            catch (Exception ex)
            {
                LogToFile.WriteException(ex, "Erreur d'execution, requete : " + cmd.CommandText, "ExecReader");
                throw ex;
            }

            return this._reader;
        }

        /// <summary>
        /// Execute a request with no return
        /// </summary>
        /// <param name="strbRequest">The request to execute in DB</param>
        public void ExecNonQuery(StringBuilder strbRequest)
        {
            ExecNonQuery(new SqlCommand(strbRequest.ToString()));
        }

        /// <summary>
        /// Execute a request with no return
        /// </summary>
        /// <param name="strbRequest">The request to execute in DB</param>
        public void ExecNonQuery(SqlCommand cmd)
        {
            try
            {
                if (this._isConOpen)
                {
                    this.ReleaseReader();
                    this._cmd = cmd;
                    this._cmd.CommandTimeout = (this._timeout != 0 ? this._timeout : this._cmd.CommandTimeout);
                    this._cmd.Connection = this._con;
                    this._cmd.ExecuteNonQuery();
                }
                else
                {
                    LogToFile.WriteError("[DBConnection.ExecNonQuery] Trying to Execute a NonQuery while the Connection is Closed.");
                }
            }
            catch (Exception ex)
            {
                LogToFile.WriteException(ex, "Erreur d'execution, requete : " + cmd.CommandText, "ExecNonQuery");
                throw ex;
            }
        }

        public Object ExecScalar(StringBuilder strbRequest)
        {
            return ExecScalar(new SqlCommand(strbRequest.ToString()));
        }

        public Object ExecScalar(SqlCommand cmd)
        {
            Object obj = new object();
            try
            {
                if (this._isConOpen)
                {
                    this.ReleaseReader();
                    this._cmd = cmd;
                    this._cmd.Connection = this._con;
                    this._cmd.CommandTimeout = (this._timeout != 0 ? this._timeout : this._cmd.CommandTimeout);
                    obj = this._cmd.ExecuteScalar();
                    this._isReaderOpen = true;
                }
                else
                {
                    LogToFile.WriteError(" [DBConnection.ExecReader] Trying to Execute a Query while the Connection is Closed. ");
                }
            }
            catch (Exception ex)
            {
                LogToFile.WriteException(ex, "Erreur d'execution, requete : " + cmd.CommandText, "ExecReader");
                throw ex;
            }
            return obj;
        }

        public void SetCmdTimeout(int seconds)
        {
            this._timeout = seconds;
        }

        /// <summary>
        /// Close the Reader
        /// </summary>
        public void ReleaseReader()
        {
            if (this._isReaderOpen)
            {
                this._reader.Close();
                this._reader.Dispose();
                this._isReaderOpen = false;
            }
        }

        /// <summary>
        /// Start the connection
        /// </summary>
        public void StartConnection()
        {
            if (!this._isConOpen)
            {
                this._con.Open();
                this._isConOpen = true;
            }
            else
            {
                LogToFile.WriteError("[DBConnection.StartConnection] Trying to Open a connection that is already open.");
            }
        }

        /// <summary>
        /// Close the connection to the DB
        /// </summary>
        public void EndConnection()
        {
            if (this._isConOpen)
            {
                this.ReleaseReader();
                this._con.Close();
                this._con.Dispose();
                this._isConOpen = false;
            }
        }

        public void SetConnectionString(string conString)
        {
            this.EndConnection();
            this._connString = conString;
            this._con = new SqlConnection(this._connString);
        }

        /// <summary>
        /// Get or Set the Reader
        /// </summary>
        public SqlDataReader Reader
        {
            get { return _reader; }
            set { _reader = value; }
        }
    }
}
